﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;
using CNative.Utilities;

namespace CNative.DbUtils
{
    internal class MySqlProvider : BaseProvider
    {
        public MySqlProvider(IDbHelper _db) : base(_db)
        {
        }
        #region 关键字
        /// <summary>
        /// 数据库类型
        /// </summary>
        public override DatabaseType DBType { get { return DatabaseType.MySql; } }
        /// <summary>
        /// 参数关键字 ?
        /// </summary>
        public override string ParamKeyword
        {
            get
            {
                return "?";
            }
        }
        /// <summary>
        /// 关键字前缀 `
        /// </summary>
        public override string SuffixLeft
        {
            get
            {
                return "`";
            }
        }
        /// <summary>
        /// 关键字后缀 `
        /// </summary>
        public override string SuffixRigh
        {
            get
            {
                return "`";
            }
        }
        #endregion

        #region DbProviderFactory
        /// <summary>
        /// 获取数据源类的提供者实例
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public override System.Data.Common.DbProviderFactory DbProviderFactory
        {
            get
            {
                return MySql.Data.MySqlClient.MySqlClientFactory.Instance;
            }
        }
        public override string GetSchemaTableName(Type tableType)
        {
            var tb = Funs.GetDbTableInfo(dbHelper, tableType);
            if (tb == null)
            {
                return "";
            }
            return tb.Schema.IsNullOrEmpty() ? tb.TableName : tb.Schema + "." + tb.TableName;
        }
        #endregion

        #region GetDbParameter
        /// <summary>
        /// 获取DbParameter
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public override IDataParameter GetDbParameter(string propName, object val, string paramSuffix = "exp_",
            DbTableInfo tb = null, ParameterDirection direction = ParameterDirection.Input, DbType valDBType = DbType.String)
        {
            IDataParameter para = null;
            //--------------------------------------------------------------------------------------------------------------
            var frets = FillerParameter(propName, val, paramSuffix);
            if (frets.Item1)
            {
                return para;
            }
            var parameterName = frets.Item2;
            val = frets.Item3;
            //--------------------------------------------------------------------------------------------------------------
            #region MySql
            if (direction == ParameterDirection.Output)
            {
                para = new MySql.Data.MySqlClient.MySqlParameter()
                {
                    ParameterName = parameterName,
                    Value = val,
                    DbType = valDBType,
                    Direction = direction
                };
                return para;
            }

            para = new MySql.Data.MySqlClient.MySqlParameter()
            {
                ParameterName = parameterName,
                Value = val,
            };
            if (tb != null && tb.TableInfo != null)
            {
                var dtinfo = tb.TableInfo.Find(f => f.Name.Equals(propName.Trim(), StringComparison.OrdinalIgnoreCase));
                if (dtinfo != null)
                {
                    try
                    {
                        (para as MySql.Data.MySqlClient.MySqlParameter).MySqlDbType = MySqlTypeString2SqlType(dtinfo.Type);// dtinfo.Type.ToEnum<MySqlDbType>();
                        if (dtinfo.MaxLength > 0 && dtinfo.MaxLength < int.MaxValue)
                            (para as MySql.Data.MySqlClient.MySqlParameter).Size = (int)dtinfo.MaxLength;
                    }
                    catch { }
                }
            }
            #endregion
            return para;
        }
        private MySql.Data.MySqlClient.MySqlDbType MySqlTypeString2SqlType(string sqlTypeString)
        {
            var dbType = MySql.Data.MySqlClient.MySqlDbType.String; //默认为Object
            sqlTypeString = sqlTypeString.Trim().ToLower();
            #region switch (sqlTypeString)
            //switch (sqlTypeString)
            //{
            //    case "int":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Int32;
            //        break;
            //    case "varchar":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
            //        break;
            //    case "bit":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Bit;
            //        break;
            //    case "datetime":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.DateTime;
            //        break;
            //    case "date":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Date;
            //        break;
            //    case "time":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Time;
            //        break;
            //    case "datetime2":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.DateTime;
            //        break;
            //    case "datetimeoffset":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Timestamp;
            //        break;
            //    case "decimal":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Decimal;
            //        break;
            //    case "udt":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Timestamp;
            //        break;
            //    case "float":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Float;
            //        break;
            //    case "image":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Binary;
            //        break;
            //    case "money":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Double;
            //        break;
            //    case "ntext":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.LongText;
            //        break;
            //    case "nvarchar":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarString;
            //        break;
            //    case "smalldatetime":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Date;
            //        break;
            //    case "smallint":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.UInt16;
            //        break;
            //    case "text":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Text;
            //        break;
            //    case "bigint":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Int64;
            //        break;
            //    case "binary":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Binary;
            //        break;
            //    case "char":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
            //        break;
            //    case "nchar":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarChar;
            //        break;
            //    case "numeric":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Decimal;
            //        break;
            //    case "real":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Float;
            //        break;
            //    case "smallmoney":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Double;
            //        break;
            //    case "timestamp":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Timestamp;
            //        break;
            //    case "tinyint":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Bit;
            //        break;
            //    case "guid":
            //    case "uniqueidentifier":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.Guid;
            //        break;
            //    case "varbinary":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.VarBinary;
            //        break;
            //    case "json":
            //        dbType = MySql.Data.MySqlClient.MySqlDbType.JSON;
            //        break;
            //    default:
            //        dbType = sqlTypeString.ToEnum<MySql.Data.MySqlClient.MySqlDbType>();
            //        break;
            //}
            #endregion
            var parameterType = GetParameterTypeName(sqlTypeString);
            dbType = parameterType.ToEnum<MySql.Data.MySqlClient.MySqlDbType>();
            return dbType;
        }
        #endregion
        #region MappingTypes
        /// <summary>
        /// 类型映射
        /// sqlTypeName，CsharpType，ParameterType
        /// </summary>
        public override List<Tuple<string, string, string>> MappingTypes
        {
            get
            {
                return new List<Tuple<string, string, string>>()
                    {
                        Tuple.Create("bit", "bool", "Bit"),
                        Tuple.Create("bigint", "long", "Int64"),
                        Tuple.Create("tinyint", "byte", "Bit"),
                        Tuple.Create("datetime", "DateTime", "Datetime"),
                        Tuple.Create("date", "DateTime", "Date"),
                        Tuple.Create("smalldatetime", "DateTime", "Date"),
                        Tuple.Create("time", "DateTime", "Time"),
                        Tuple.Create("timestamp", "string", "Timestamp"),
                        Tuple.Create("datetime2", "DateTime", "DateTime"),
                        Tuple.Create("datetimeoffset", "DateTime", "Timestamp"),
                        Tuple.Create("udt", "DateTime", "Timestamp"),
                        Tuple.Create("decimal", "decimal", "Decimal"),
                        Tuple.Create("money", "double", "Double"),
                        Tuple.Create("smallmoney", "double", "Double"),
                        Tuple.Create("binary", "byte[]", "Binary"),
                        Tuple.Create("blob", "byte[]", "Blob"),
                        Tuple.Create("longblob", "byte[]", "LongBlob"),
                        Tuple.Create("double", "double", "Double"),
                        Tuple.Create("float", "decimal", "Float"),
                        Tuple.Create("int", "int", "Int32"),
                        Tuple.Create("smallint", "short", "UInt16"),
                        Tuple.Create("image", "byte[]", "Binary"),
                        Tuple.Create("numeric", "decimal", "Decimal"),
                        Tuple.Create("real", "decimal", "Float"),
                        Tuple.Create("text", "string", "Text"),
                        Tuple.Create("ntext", "string", "LongText"),
                        Tuple.Create("longtext", "string", "LongText"),
                        Tuple.Create("char", "string", ",VarChar"),
                        Tuple.Create("nchar", "string", ",VarChar"),
                        Tuple.Create("varchar", "string", ",VarChar"),
                        Tuple.Create("nvarchar", "string", ",VarString"),
                        Tuple.Create("guid", "Guid", ",Guid"),
                        Tuple.Create("uniqueidentifier", "Guid", ",Guid"),
                        Tuple.Create("varbinary", "byte[]", ",VarBinary")
                    };
            }
        }

        /// <summary>
        /// 获取库类型
        /// </summary>
        /// <param name="csharpTypeName"></param>
        /// <returns></returns>
        public override string GetDbTypeName(string csharpTypeName)
        {
            if (csharpTypeName == Constants.ByteArrayType.Name)
                return "blob";
            csharpTypeName = CheckCsharpTypeName(csharpTypeName);
            var mappings = this.MappingTypes?.Where(it => it.Item2.Equals(csharpTypeName, StringComparison.CurrentCultureIgnoreCase)).ToList();
            if (mappings != null && mappings.Count > 0)
                return mappings.First().Item1;
            else
                return "varchar";
        }
        /// <summary>
        /// 获取sql Parameter Type
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <returns></returns>
        public override string GetParameterTypeName(string dbTypeName)
        {
            var mappings = this.MappingTypes?.Where(it => it.Item1.Equals(dbTypeName, StringComparison.CurrentCultureIgnoreCase));
            return !mappings.IsNullOrEmpty_() ? mappings.First().Item3 : "VarChar";
        }
        #endregion
        #region Sql Fun Templet
        /// <summary>
        /// SQL NVL() 从两个表达式返回一个非 null 值	函数
        /// </summary>
        /// <param name="check_expression">将被检查是否为 NULL的表达式。可以是任何类型的。</param>
        /// <param name="replacement_value">在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。</param>
        /// <returns>如果 check_expression 不为 NULL，那么返回该表达式的值；否则返回 replacement_value</returns>
        public override string SQL_NVL(string check_expression, string replacement_value)
        {
            return $"IFNULL({check_expression},{replacement_value})";
        }
        /// <summary>
        /// 获取数据库时间函数
        /// </summary>
        public override string SqlDateNow
        {
            get
            {
                return "sysdate()"; //" NOW() ";
            }
        }
        /// <summary>
        /// 获取当前时间sql语句
        /// </summary>
        public override string FullSqlDateNow
        {
            get
            {
                return "select sysdate()";
            }
        }

        /// <summary>
        /// SQL UCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为大写</returns>
        public override string SQL_UCASE(string column_name)
        {
            return $"UPPER({column_name})";
        }

        /// <summary>
        /// SQL LCASE() 函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <returns>函数把字段的值转换为小写</returns>
        public override string SQL_LCASE(string column_name)
        {
            return $"LOWER({column_name})";
        }

        /// <summary>
        /// SQL LEFT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_LEFT(string str, int length)
        {
            return $"LEFT({str},{length})";
        }
        /// <summary>
        /// SQL RIGHT(s,n) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>返回字符串 str 的前 length 个字符</returns>
        public override string SQL_RIGHT(string str, int length)
        {
            return $"RIGHT({str},{length})";
        }
        /// <summary>
        /// SQL SUBSTR(s, start, length) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>从字符串 str 的 start 位置截取长度为 length 的子字符串</returns>
        public override string SQL_SUBSTR(string str, int start, int length)
        {
            return $"SUBSTR({str},{1 + start},{length})";
        }
        /// <summary>
        /// SQL TRIM(s) 函数
        /// </summary>
        /// <param name="str">列名</param>
        /// <returns>去掉字符串 str 开始和结尾处的空格</returns>
        public override string SQL_TRIM(string str)
        {
            return $"TRIM({str})";
        }
        /// <summary>
        /// SQL CAST() 转换数据类型	函数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="column_name">列名</param>
        /// <param name="type">数据类型</param>
        /// <returns>转换数据类型</returns>
        public override string SQL_CAST(string column_name, string type)
        {
            return $"CAST({column_name} AS {type})";
        }

        public override string SQL_ToInt32(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }

        public override string SQL_ToInt64(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }
        public override string SQL_ToDouble(string column_name)
        {
            return SQL_CAST(column_name, "DECIMAL(18,4)");
        }
        /// <summary>转换高精度的十进制数（一般用于货币）</summary>
        /// <param name="column_name"></param>
        /// <param name="d">小数位数</param>
        /// <returns></returns>
        public override string SQL_ToDecimal(string column_name, int d)
        {
            if (d < 0 || d > 10) d = 4;
            return SQL_CAST(column_name, "DECIMAL(18, " + d + ")");
        }

        public override string SQL_ToBool(string column_name)
        {
            return SQL_CAST(column_name, "SIGNED");
        }

        public override string SQL_ToVarchar(string column_name)
        {
            return SQL_CAST(column_name, "CHAR");
        }

        public override string SQL_ToGuid(string column_name)
        {
            return SQL_CAST(column_name, "CHAR");
        }

        public override string SQL_ToDate(string column_name)
        {
            return SQL_CAST(column_name, "DATETIME");
        }

        public override string SQL_ToDateShort(string column_name)
        {
            return SQL_CAST(column_name, "DATE");
        }

        public override string SQL_ToTime(string column_name)
        {
            return SQL_CAST(column_name, "TIME");
        }
        #endregion

        #region SqlTemplet
        /// <summary>
        /// 一般查询语句模板
        /// </summary>
        /// <param name="tbname"></param>
        /// <param name="fieldsstr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="top"></param>
        /// <returns></returns>
        public override string GetSelectSQL(String tbname, string fieldsstr, string orderbyStr, int top = 0)
        {
            var sqlMeta = @"SELECT " + fieldsstr + " FROM " + tbname + " WHERE {0} " + orderbyStr + (top > 0 ? " limit 0, " + top + " " : "");
            return sqlMeta;
        }
        /// <summary>
        /// 分页查询语句模板
        /// </summary>
        /// <param name="tbName">表名</param>
        /// <param name="fieldsstr"></param>
        /// <param name="whereStr"></param>
        /// <param name="orderbyStr"></param>
        /// <param name="page">当前页面</param>
        /// <param name="nums">每页记录数</param>
        /// <returns></returns>
        public override string GetSelectPageSQL(string tbName, string fieldsstr, string whereStr, string orderbyStr, int page = 1, int nums = 25)
        {
            if (page < 1) page = 1;
            if (nums < 1) nums = 10;
            var m = (page - 1) * nums;// + 1;
            var n = nums;// page * nums;

            var sqlMeta = $"SELECT {fieldsstr} FROM {tbName} WHERE {whereStr } {orderbyStr} limit {m},{n}";
            return sqlMeta;
        }
        /// <summary>
        /// MERGE 合并语句模板
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="tbname"></param>
        /// <returns></returns>
        public override string GetMergeSql(String tbname)
        {
            var sqlMeta = @"MERGE " + tbname + " AS [target]  USING(SELECT 1 AS ____Id_____) AS source ON {0}  WHEN MATCHED THEN {1}  WHEN NOT MATCHED THEN {2};";

            return sqlMeta;
        }

        /// <summary>
        /// 更新语句模板
        /// "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ")
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetUpdateSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "UPDATE " + (isMerge ? "" : fromStr) + " SET {0} " + (isMerge ? "" : " WHERE {1} ");

            return sqlMeta;
        }
        /// <summary>
        /// 插入语句模板
        /// "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})"
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="isMerge">是参与MERGE</param>
        /// <returns></returns>
        public override string GetInsertSql(String fromStr, bool isMerge = false)
        {
            var sqlMeta = "INSERT " + (isMerge ? "" : " INTO " + fromStr) + " ({0}) VALUES ({1})";

            return sqlMeta;
        }
        /// <summary>
        /// 统计数量语句模板
        /// $"SELECT Count(0) FROM {fromStr} WHERE {0}";
        /// </summary>
        /// <param name="_dbType"></param>
        /// <param name="fromStr"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public override string GetCountSql(String fromStr, string fieldName = "")
        {
            var sqlMeta = "SELECT Count(" + (fieldName.IsNullOrEmpty() ? "0" : fieldName) + ") FROM " + fromStr + " WHERE {0}";

            return sqlMeta;
        }
        /// <summary>
        /// GetInsertSelectSql
        /// INSERT INTO " + insertTable + " ({0}) " + " SELECT {1} FROM " + selectTable + "  WHERE {2} 
        /// </summary>
        /// <param name="insertTable"></param>
        /// <param name="selectTable"></param>
        /// <param name="_dbType"></param>
        /// <returns></returns>
        public override string GetInsertSelectSql(String insertTable, String selectTable)
        {
            var sqlMeta = "INSERT INTO " + insertTable + " ({0}) " +
                            " SELECT {1} FROM " + selectTable + "  WHERE {2} ";

            return sqlMeta;
        }
        #endregion

        #region WithNextSequence
        /// <summary>
        /// 下个序列脚本
        /// </summary>
        /// <param name="sequenceName"></param>
        /// <returns></returns>
        public override string WithNextSequence(string sequenceName = null)
        {
            if (string.IsNullOrEmpty(sequenceName))
            {
                sequenceName = "default_seq";
            }
            return $"nextval('{sequenceName}')";
        }
        #endregion

        #region DML CodeFirst
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        public override string GetDataBaseSql
        {
            get
            {
                return "SHOW DATABASES";
            }
        }
        /// <summary>
        /// 获取数据库中的所有表
        /// </summary>
        public override string GetTableInfoListSql
        {
            get
            {
                return @"select TABLE_NAME as name,TABLE_COMMENT as Description from information_schema.tables
                         where  TABLE_SCHEMA='{0}'  AND TABLE_TYPE='BASE TABLE'";
            }
        }
        /// <summary>
        /// 获取数据库中的所有视图
        /// </summary>
        public override string GetViewInfoListSql
        {
            get
            {
                return @"select TABLE_NAME as name,TABLE_COMMENT as Description from information_schema.tables
                         where  TABLE_SCHEMA='{0}' AND TABLE_TYPE='VIEW'";
            }
        }
        /// <summary>
        /// 得到一个表的所有列信息
        /// </summary>
        public override string GetColumnInfosByTableNameSql
        {
            get
            {
                return @"SELECT
                        TABLE_NAME as TableName, 
                        CONCAT(table_schema, '.', table_name) as  `table_name`,
		                ORDINAL_POSITION AS `ColumnId`,
                        column_name AS `Name`,
                        CASE WHEN  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1)='' THEN COLUMN_TYPE ELSE  left(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)-1) END   AS `Type`,
                        CAST(SUBSTRING(COLUMN_TYPE,LOCATE('(',COLUMN_TYPE)+1,LOCATE(')',COLUMN_TYPE)-LOCATE('(',COLUMN_TYPE)-1) AS signed) AS MaxLength,
                        column_default  AS  `DefaultValue`,
                        column_comment  AS  `Description`,
                        CASE WHEN COLUMN_KEY = 'PRI'
                        THEN true ELSE false END AS `IsPrimaryKey`,
                        CASE WHEN EXTRA='auto_increment' THEN true ELSE false END as IsIdentity,
                        CASE WHEN is_nullable = 'YES'
                        THEN true ELSE false END AS `IsNullable`,
                        numeric_scale as Scale,
                        numeric_scale as DecimalDigits,
                        COLUMN_TYPE as DbFullType
                        FROM
                        Information_schema.columns where TABLE_NAME='{1}' and  TABLE_SCHEMA='{0}' ORDER BY ORDINAL_POSITION;";
            }
        }
        #endregion
    }
}
